//-----------------------------------------------------------------------------
//クラス名：AttendanceYearHistory.java
//所    属：H3A1 04
//名    前：大瀬碩
//説    明：年度の出席履歴を表示する
//-----------------------------------------------------------------------------
package attendance_history;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Calendar;
import java.util.Properties;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
//import javax.servlet.annotation.WebServlet;

//@WebServlet("/AttendanceYearHistory")
public class AttendanceYearHistory extends HttpServlet {
	private static final long serialVersionUID = 1L;

	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		response.setContentType("text/html; charset=UTF-8");

		HttpSession session = request.getSession(true);
		
		Calendar calendar = Calendar.getInstance();
	    int year = calendar.get(Calendar.YEAR);
	    int month = calendar.get(Calendar.MONTH)+1;
	    int nend = year;
	    if(month<=3){
	    	nend = year-1;
	    }
	    
	    //DB接続
	    ServletContext context = this.getServletContext();
		String path = context.getRealPath("/WEB-INF/connect.properties");
		
		InputStream in = new FileInputStream(path);
		Properties properties = new Properties();
		properties.load(in);
		in.close();
	    
	    Connection conn = null;
	    String url = properties.getProperty("key1");
	    String user = properties.getProperty("key2");
	    String password = properties.getProperty("key3");
	    
	    Object id = session.getAttribute("id");
	    Object name = session.getAttribute("name");
	    Object an = session.getAttribute("a_number");
	    Object class_id = session.getAttribute("class");
	    
	    int at=0;
	    int ta=0;
	    int af=0;
	    int acflag=0;
	    
	    request.setAttribute("name",name);
	    request.setAttribute("an",an);
	    request.setAttribute("afs", af);
	    request.setAttribute("tardy", ta);
	    request.setAttribute("attendance", at);
	    request.setAttribute("sc", acflag);
	    
	    try {
	      Class.forName("com.mysql.jdbc.Driver").newInstance();
	      conn = DriverManager.getConnection(url, user, password);

	      Statement stmt = conn.createStatement();
	      String sql = "SELECT * FROM class WHERE class_id = "+class_id;
	      ResultSet rs = stmt.executeQuery(sql);
	      while(rs.next()){
	    	  request.setAttribute("id",rs.getString("class_name"));
	      }
	      
	      //DBから情報を取得
	      sql = "SELECT * FROM attendance_records WHERE date BETWEEN '"+nend+"/4/1' AND '"+year+"/3/31'"+
	      " AND student_id = "+id;
	      rs = stmt.executeQuery(sql);
	      while(rs.next()){
	        String status = rs.getString("status_id");
	        if(status.equals("1")){
	        	at++;
	        	request.setAttribute("attendance", at);
	        }
	        if(status.equals("2")){
	        	ta++;
	        	request.setAttribute("tardy", ta);
	        }
	        if(status.equals("3")){
	        	af++;
	        	request.setAttribute("afs", af);
	        }
	        if(status.equals("4")){
	        	at++;
	        	request.setAttribute("attendance", at);
	        }
	      }
	      
	      //補講回数を取得
	      sql = "SELECT * FROM makeup_class WHERE acceptance_date BETWEEN '"+nend+"/4/1' AND '"+year+"/4/31'"+
	    	      " AND student_id = "+id;
	      rs = stmt.executeQuery(sql);
	      
	      
	      while(rs.next()){
	    	  String status = rs.getString("acceptance_flag");
		        if(status.equals("1")){
		        	acflag++;
		        	request.setAttribute("sc", acflag);
		        }
		        if(status.equals("2")){
		        	acflag=acflag+2;
		        	request.setAttribute("sc", acflag);
		        }
		        if(status.equals("3")){
		        	acflag=acflag+3;
		        	request.setAttribute("sc", acflag);
		        }
		        if(status.equals("4")){
		        	acflag=acflag+4;
		        	request.setAttribute("sc", acflag);
		        }
	      }
	      
	      rs.close();
	      stmt.close();
	    }catch (ClassNotFoundException e){
	    	
	    }catch (SQLException e){
	      
	    }catch (Exception e){
	    	
	    }finally{
	      try{
	        if (conn != null){
	          conn.close();
	        }
	      }catch (SQLException e){
	        
	      }
	    }
	    
	    RequestDispatcher rd = request.getRequestDispatcher("AttendanceYearHistory.jsp");
		rd.forward(request,response);
	}

}
